﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListSermon')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListSermon'
    DROP  Procedure  proc_cm_ListSermon
END
GO

PRINT 'Creating Procedure proc_cm_ListSermon'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListSermon]
	@pVideoTypeId int = null
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		s.[sermon_id]
		,s.[sermon_series_id]
		,ss.[series_nm]
		,s.[title_txt]
		,s.[video_type_id]
		,s.[passage_txt]
		,s.[preached_dttm]
		,s.[create_dttm]
		,s.[update_dttm]
		,s.[Youtube_url]
		,s.[preacher_nm]
		,s.[reference_txt]
		,s.[download_filename_txt] 
	FROM 
		[dbo].[tbl_sermon] s (nolock)
		LEFT JOIN [dbo].[tbl_sermon_series] ss (nolock) on s.sermon_series_id = ss.sermon_series_id
	WHERE
		@pVideoTypeId is null or (
			s.[video_type_id] = @pVideoTypeId
			and s.[Youtube_url] is not null
		)
	ORDER BY
		s.[preached_dttm] DESC
END
GO

GRANT EXEC ON dbo.proc_cm_ListSermon TO PUBLIC
GO
